package com.apache.flow.core.dao;

import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.hibernate.dialect.Dialect;
import org.hibernate.impl.SessionFactoryImpl;
import org.springframework.stereotype.Repository;

import com.apache.common.Native2AsciiUtils;
import com.apache.core.orm.Page;
import com.apache.core.orm.hibernate.ApacheBaseDao;
import com.apache.core.utils.SpringContextHolder;
import com.apache.flow.core.entity.FlowDeploy;
import com.apache.flow.vo.FlowDeployvo;
import com.apache.tools.StrUtil;

@Repository
public class FlowDeployDao extends ApacheBaseDao<FlowDeploy, String> {

	/**
	 * 
	 * @param flowDeployvo VO对象
	 * @param pageNo 当前页
	 * @param pageSize 每页记录数
	 * @return
	 * <p>@description 流程发布获取发布记录信息
	 * <p>@version 1.0
	 * <p>@author lvcai
	 * <p>@update 2013-7-29 下午03:57:24
	 */
	public Page<FlowDeployvo> getFlowDeployInfo(FlowDeployvo flowDeployvo, int pageNo, int pageSize) {
		Page<FlowDeployvo> page = new Page<FlowDeployvo>();
		page.setPageNo(pageNo);
		page.setPageSize(pageSize);

		StringBuffer sb = new StringBuffer("select - from FlowDeploy d where 1=1 ");
		if (StrUtil.isNotNull(flowDeployvo.getProcessName())) {
			sb.append(" and d.processName like '%").append(flowDeployvo.getProcessName()).append("%'");
		}
		if (StrUtil.isNotNull(flowDeployvo.getProcessStatus())) {
			sb.append(" and d.processStatus ='").append(flowDeployvo.getProcessStatus()).append("'");
		}
		if (StrUtil.isNotNull(flowDeployvo.getOrgId())) {
			sb.append(" and d.orgId ='").append(flowDeployvo.getOrgId()).append("'");
		}

		sb.append(" order by d.processStatus desc,d.createTime desc");
		Query query = this
				.getSession()
				.createQuery(
						sb.toString()
								.replaceFirst(
										"-",
										" new com.apache.flow.vo.FlowDeployvo(d.sysCname,d.dbId,d.sysEname,d.processName,d.fileName,d.pictureName,d.processDefinitionId,d.processDeploymentId,d.processKey,d.processVersion,d.processSpare,d.processStatus,d.createTime,d.orgId,d.orgCname) "));
		System.out
				.println(sb
						.toString()
						.replaceFirst(
								"-",
								" new com.apache.flow.vo.FlowDeployvo(d.sysCname,d.dbId,d.sysEname,d.processName,d.fileName,d.pictureName,d.processDefinitionId,d.processDeploymentId,d.processKey,d.processVersion,d.processSpare,d.processStatus,d.createTime,d.orgId,d.orgCname) "));
		query.setFirstResult((Integer.valueOf(pageNo) - 1) * Integer.valueOf(pageSize));
		query.setMaxResults(Integer.valueOf(pageSize));
		List<FlowDeployvo> list = query.list();
		page.setResult(list);

		Query query_count = this.getSession().createQuery(sb.toString().replaceFirst("-", "count(d.dbId)"));
		page.setTotalCount(Long.valueOf(query_count.list().get(0).toString()));

		return page;
	}

	/**
	 * 
	 * @param subTaskId  taskId
	 * @param taskAssignee  当前经办人
	 * <p>@description 修改历史表中，经办人信息
	 * <p>@version 1.0
	 * <p>@author lvcai/yxcui
	 * <p>@updateInfo :mysql+concat连接符
	 * <p>@update 2014年8月5日14:57:44
	 */
	public void update(String subTaskId, String taskAssignee) {
		SpringContextHolder sch = new SpringContextHolder();
		SessionFactoryImpl sessionFactory = (SessionFactoryImpl) sch.getApplicationContext().getBean(
				SessionFactory.class);
		Dialect dia = sessionFactory.getDialect();
		String dialectStr = dia.toString();
		String sql = "";
		if (dialectStr.contains("MySQL") || dialectStr.contains("mysql")) {
			sql = "update jbpm4_hist_task t set t.assignee_=t.assignee_||concat('," + taskAssignee
					+ "') where t.dbid_ = (select tt.supertask_ from jbpm4_task tt where tt.dbid_='" + subTaskId + "')";
		} else {
			sql = "update jbpm4_hist_task t set t.assignee_=t.assignee_||'," + taskAssignee
					+ "' where t.dbid_ = (select tt.supertask_ from jbpm4_task tt where tt.dbid_='" + subTaskId + "')";
		}
		this.getSession().createSQLQuery(sql).executeUpdate();
	}

	/**
	 * 
	 * @return
	 * <p>@description 综合查询待办任务
	 * <p>@version 1.0
	 * <p>@author lvcai
	 * <p>@update 2013-11-16 上午11:25:58
	 */
	public Page<Object[]> findTasksByOptions(Map<String, String> map) {
		StringBuffer sb = new StringBuffer("select - from jbpm4_task t where t.assignee_ is not null");

		if (StrUtil.isNotNull(map.get("assignee"))) {//审核人
			sb.append(" and t.assignee_='" + map.get("assignee").trim() + "'");
		}

		if (StrUtil.isNotNull(map.get("state"))) {//状态
			sb.append(" and t.state_='" + map.get("state").trim() + "'");
		}

		if (StrUtil.isNotNull(map.get("taskName"))) {//节点名称
			sb.append(" and t.name_='" + map.get("taskName").trim() + "'");
		}

		if (StrUtil.isNotNull(map.get("processInsId"))) {//流程实例Id
			sb.append(" and t.execution_id_='" + map.get("processInsId").trim() + "'");
		}
		//2015年3月26日16:44:21
		//崔永旭
		//修改mysql 获取综合待办任务
		//以下为参考sql
		/**
		 * (一下sql在mysql执行能够获取系统名称)
		 select  
			substring_index(t.descr_,'~~#',-3) as num,
			instr(substring_index(t.descr_,'~~#',-3),'~~#')-1 as str ,
			left(substring_index(t.descr_,'~~#',-3),instr(substring_index(t.descr_,'~~#',-3),'~~#')-1) as mt
			from jbpm4_task t 
			where t.assignee_ is not null 
			and t.assignee_='uni' 
			and t.state_='open' 
		 */
		SpringContextHolder sch = new SpringContextHolder();
		SessionFactoryImpl sessionFactory = (SessionFactoryImpl) sch.getApplicationContext().getBean(
				SessionFactory.class);
		Dialect dia = sessionFactory.getDialect();
		String dialectStr = dia.toString();
		if (dialectStr.contains("MySQL") || dialectStr.contains("mysql")) {
			if (StrUtil.isNotNull(map.get("startDate"))) {//开始时间
				sb.append(" and date_format(t.create_,'%Y%m%d')>='" + map.get("startDate").trim() + "'");
			}
			if (StrUtil.isNotNull(map.get("endDate"))) {//结束时间
				sb.append(" and date_format(t.create_,'%Y%m%d')<='" + map.get("endDate").trim() + "'");
			}
			if (StrUtil.isNotNull(map.get("formId"))) {//业务Id
				sb.append("and (left(substring_index(t.descr_,'~~#',-9),instr(substring_index(t.descr_,'~~#',-9),'~~#')-1))='"
						+ map.get("formId").trim() + "'");
			}

			if (StrUtil.isNotNull(map.get("formName"))) {//业务名称
				sb.append("and (left(substring_index(t.descr_,'~~#',-8),instr(substring_index(t.descr_,'~~#',-8),'~~#')-1)) like '%"
						+ map.get("formName").trim() + "%'");
			}

			if (StrUtil.isNotNull(map.get("formInfo"))) {//业务补充信息
				sb.append("and (left(substring_index(t.descr_,'~~#',-7),instr(substring_index(t.descr_,'~~#',-7),'~~#')-1)) like '%"
						+ map.get("formInfo").trim() + "%'");
			}

			if (StrUtil.isNotNull(map.get("formType"))) {//业务类型
				sb.append("and (left(substring_index(t.descr_,'~~#',-6),instr(substring_index(t.descr_,'~~#',-6),'~~#')-1)) like '%"
						+ map.get("formType").trim() + "%'");
			}

			if (StrUtil.isNotNull(map.get("lastUser"))) {//上一审核人
				sb.append("and (left(substring_index(t.descr_,'~~#',-5),instr(substring_index(t.descr_,'~~#',-5),'~~#')-1)) like '%"
						+ map.get("lastUser").trim() + "%'");
			}

			if (StrUtil.isNotNull(map.get("userName"))) {//任务提交人
				sb.append("and (left(substring_index(t.descr_,'~~#',-4),instr(substring_index(t.descr_,'~~#',-4),'~~#')-1)) like '%"
						+ map.get("userName").trim() + "%'");
			}

			if (StrUtil.isNotNull(map.get("sysEname"))) {//系统标识
				sb.append(" and (left(substring_index(t.descr_,'~~#',-3),instr(substring_index(t.descr_,'~~#',-3),'~~#')-1)) like '%"
						+ map.get("sysEname") + "%'");
			}

		} else {
			if (StrUtil.isNotNull(map.get("startDate"))) {//开始时间
				sb.append(" and to_char(t.create_,'YYYY-MM-DD')>='" + map.get("startDate").trim() + "'");
			}

			if (StrUtil.isNotNull(map.get("endDate"))) {//结束时间
				sb.append(" and to_char(t.create_,'YYYY-MM-DD')<='" + map.get("endDate").trim() + "'");
			}

			if (StrUtil.isNotNull(map.get("formId"))) {//业务Id
				sb.append(" and substr(to_char(t.descr_),instr(to_char(t.descr_),'~~#',1,1)+3,instr(to_char(t.descr_),'~~#',1,2)-(instr(to_char(t.descr_),'~~#',1,1)+3))='"
						+ map.get("formId").trim() + "'");
			}

			if (StrUtil.isNotNull(map.get("formName"))) {//业务名称
				sb.append(" and substr(to_char(t.descr_),instr(to_char(t.descr_),'~~#',1,2)+3,instr(to_char(t.descr_),'~~#',1,3)-(instr(to_char(t.descr_),'~~#',1,2)+3)) like '%"
						+ map.get("formName").trim() + "%'");
			}

			if (StrUtil.isNotNull(map.get("formInfo"))) {//业务补充信息
				sb.append(" and substr(to_char(t.descr_),instr(to_char(t.descr_),'~~#',1,3)+3,instr(to_char(t.descr_),'~~#',1,4)-(instr(to_char(t.descr_),'~~#',1,3)+3)) like '%"
						+ map.get("formInfo").trim() + "%'");
			}

			if (StrUtil.isNotNull(map.get("formType"))) {//业务类型
				sb.append(" and substr(to_char(t.descr_),instr(to_char(t.descr_),'~~#',1,4)+3,instr(to_char(t.descr_),'~~#',1,5)-(instr(to_char(t.descr_),'~~#',1,4)+3))='"
						+ map.get("formType").trim() + "'");
			}

			if (StrUtil.isNotNull(map.get("lastUser"))) {//上一审核人
				sb.append(" and substr(to_char(t.descr_),instr(to_char(t.descr_),'~~#',1,5)+3,instr(to_char(t.descr_),'~~#',1,6)-(instr(to_char(t.descr_),'~~#',1,5)+3)) like '%"
						+ map.get("lastUser").trim() + "%'");
			}

			if (StrUtil.isNotNull(map.get("userName"))) {//任务提交人
				sb.append(" and substr(to_char(t.descr_),instr(to_char(t.descr_),'~~#',1,6)+3,instr(to_char(t.descr_),'~~#',1,7)-(instr(to_char(t.descr_),'~~#',1,6)+3))='"
						+ map.get("userName").trim() + "'");
			}

			if (StrUtil.isNotNull(map.get("sysEname"))) {//系统标识
				sb.append(" and substr(to_char(t.descr_),instr(to_char(t.descr_),'~~#',1,7)+3,instr(to_char(t.descr_),'~~#',1,8)-(instr(to_char(t.descr_),'~~#',1,7)+3))='"
						+ map.get("sysEname").trim() + "'");
			}

			if (StrUtil.isNotNull(map.get("proId"))) {//项目Id
				sb.append(" and substr(to_char(t.descr_),instr(to_char(t.descr_),'~~#',1,8)+3,instr(to_char(t.descr_),'~~#',1,9)-(instr(to_char(t.descr_),'~~#',1,8)+3))='"
						+ map.get("proId").trim() + "'");
			}
			if (StrUtil.isNotNull(map.get("proNo"))) {//项目编号
				sb.append(" and substr(to_char(t.descr_),instr(to_char(t.descr_),'~~#',1,9)+3,instr(to_char(t.descr_),'~~#',1,10)-(instr(to_char(t.descr_),'~~#',1,9)+3))='"
						+ map.get("proNo").trim() + "'");
			}
			if (StrUtil.isNotNull(map.get("proName"))) {//项目名称
				sb.append(" and substr(to_char(t.descr_),instr(to_char(t.descr_),'~~#',1,10)+3,instr(to_char(t.descr_),'~~#',1,11)-(instr(to_char(t.descr_),'~~#',1,10)+3))='"
						+ map.get("proName").trim() + "'");
			}
		}

		//获取总记录数的查询
		Query query_count = this.getSession().createSQLQuery(sb.toString().replaceFirst("-", "count(t.dbid_)"));

		sb.append(" order by  t.create_ desc");
		Query query = this.getSession().createSQLQuery(
				sb.toString().replaceFirst("-",
						" t.dbid_,t.execution_id_,t.name_,t.form_,t.create_,t.descr_,t.assignee_ "));
		System.out.println(sb.toString().replaceFirst("-",
				" t.dbid_,t.execution_id_,t.name_,t.form_,t.create_,t.descr_,t.assignee_ "));
		Page<Object[]> page = new Page<Object[]>();
		String pageNo = StrUtil.doNull(map.get("pageNo"), "");
		String pageSize = StrUtil.doNull(map.get("pageSize"), "");
		if (StrUtil.isNotNull(pageNo) && StrUtil.isNotNull(pageSize)) {
			query.setFirstResult((Integer.valueOf(pageNo) - 1) * Integer.valueOf(pageSize));
			query.setMaxResults(Integer.valueOf(pageSize));
		}

		List<Object[]> list = query.list();

		page.setTotalCount(Long.valueOf(query_count.list().get(0).toString()));
		page.setResult(list);
		return page;

	}

	/**
	 * description:  查询历史待办任务
	 * @param map
	 * @return  
	 * @author xyc 
	 * @update 2015-4-14
	 */
	public Page<Object[]> findHistTasksByOptions(Map<String, String> map) {
		StringBuffer sb = new StringBuffer(
				"select - from jbpm4_hist_actinst a inner join jbpm4_hist_procinst p on p.dbid_=a.hproci_ inner join jbpm4_hist_task t on a.htask_=t.dbid_");
		SpringContextHolder sch = new SpringContextHolder();
		SessionFactoryImpl sessionFactory = (SessionFactoryImpl) sch.getApplicationContext().getBean(
				SessionFactory.class);
		Dialect dia = sessionFactory.getDialect();
		String dialectStr = dia.toString();
		Page<Object[]> page = new Page<Object[]>();
		String assignee = map.get("assignee");
		String activityName = map.get("activityName");
		if (StrUtil.isNull(assignee)) {
			page.setTotalCount(0);
			return page;
		}
		//防止出现中文无法查询的情况
		//审核人
		assignee = Native2AsciiUtils.getInstance().ascii2Native(assignee);
		String procdefId = map.get("procdefId");//流程定义id
		if (StrUtil.isNotNull(activityName)) {
			activityName = Native2AsciiUtils.getInstance().ascii2Native(activityName);//流程实例名称
		}
		String executionId = map.get("executionId");//流程实例id
		String createDate = map.get("createDate");//开始时间
		String endDate = map.get("endDate");//结束时间

		if (StrUtil.isNotNull(assignee)) {
			sb.append(" and t.assignee_ like '%" + assignee.trim() + "%'");
		}
		if (StrUtil.isNotNull(procdefId)) {//流程定义Id
			sb.append(" and p.procdefid_='" + procdefId.trim() + "'");
		}
		if (StrUtil.isNotNull(activityName)) {//流程实例名称
			sb.append(" and a.activity_name_='" + activityName.trim() + "'");
		}
		if (StrUtil.isNotNull(executionId)) {//流程实例名称
			sb.append(" and p.id_='" + executionId.trim() + "'");
		}

		if (dialectStr.contains("MySQL") || dialectStr.contains("mysql")) {
			if (StrUtil.isNotNull(createDate)) {//开始时间
				sb.append(" and date_format(t.create_,'%Y%m%d')>='" + createDate.trim() + "'");
			}
			if (StrUtil.isNotNull(endDate)) {//结束时间
				sb.append(" and date_format(t.create_,'%Y%m%d')<='" + endDate.trim() + "'");
			}
		} else {
			if (StrUtil.isNotNull(createDate)) {//开始时间
				sb.append(" and to_char(t.create_,'YYYY-MM-DD')>='" + createDate.trim() + "'");
			}
			if (StrUtil.isNotNull(endDate)) {//结束时间
				sb.append(" and to_char(t.create_,'YYYY-MM-DD')<='" + endDate.trim() + "'");
			}
		}

		//获取总记录数的查询
		Query query_count = this.getSession().createSQLQuery(sb.toString().replaceFirst("-", "count(t.dbid_)"));
		String sql = sb.toString().replaceFirst("-",
				" p.procdefid_,p.id_,a.activity_name_,t.assignee_,t.create_,t.end_ ");
		Query query = this.getSession().createSQLQuery(sql);
		String pageNo = StrUtil.doNull(map.get("pageNo"), "");
		String pageSize = StrUtil.doNull(map.get("pageSize"), "");
		if (StrUtil.isNotNull(pageNo) && StrUtil.isNotNull(pageSize)) {
			query.setFirstResult((Integer.valueOf(pageNo) - 1) * Integer.valueOf(pageSize));
			query.setMaxResults(Integer.valueOf(pageSize));
		}

		List<Object[]> list = query.list();

		page.setTotalCount(Long.valueOf(query_count.list().get(0).toString()));
		page.setResult(list);
		return page;
	}

}
